package main

import (
	"fmt"

	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

type Db struct {
	Sql *sqlx.DB
}

func NewDB(name string) (*Db, error) {
	sql, err := sqlx.Connect("sqlite3", name)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}
	return &Db{
		Sql: sql,
	}, nil
}

// tableExists 检查给定的表是否存在于数据库中
func (me *Db) tableExist(tableName string) bool {
	var count int
	err := me.Sql.Get(&count, "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", tableName)
	if err != nil {
		return false
	}
	return count > 0
}
func (me *Db) Close() {
	me.Sql.Close()
}

func (me *Db) Init() {
	// create table
	var schema = `
CREATE TABLE Item (
    code text,
    uuid text unique not null,
    qty integer default 0,
    spec text,
    model text not null,
    brand text,
    location text,
    created_at datetime default current_timestamp,
    updated_at datetime default current_timestamp,
    PRIMARY KEY (uuid)
);
`
	if !me.tableExist("Item") {
		me.Sql.MustExec(schema)
	} else {
		// 如果表已存在，检查是否需要迁移
		me.migrateTable()
	}
}

func (me *Db) migrateTable() {
	// 检查是否存在name字段
	var count int
	err := me.Sql.Get(&count, "SELECT COUNT(*) FROM pragma_table_info('Item') WHERE name='name'")
	if err == nil && count > 0 {
		// 存在name字段，需要迁移到model
		me.Sql.MustExec("ALTER TABLE Item ADD COLUMN model text")
		me.Sql.MustExec("UPDATE Item SET model = name")
		me.Sql.MustExec("ALTER TABLE Item DROP COLUMN name")
	}
}
